﻿-- =============================================
-- Author:		<赵洪涛>
-- Create date: <2012-02-18>
-- Description:	<获得符合条件的能申领的客户数量>
-- =============================================
CREATE PROCEDURE [dbo].[proc_Customer_AllowExtract_Count] 
	(
		@UserName varchar(20),
		@IndustryOneId varchar(20),
		@IndustryTwoId varchar(20),
        @CustomerTypeId varchar(20)
	)
AS	
Begin
	Declare @companyid int,@num int,@Days int
	Set @Days = -10		--10日内批注过的客户不再参与申领
	EXEC @companyid=proc_Company_GetID @UserName,@companyid--单位ID
	If @IndustryOneId='-1'	--所有行业
			--Select Count(0) As shl From Customer Where Companyid=@companyid and Audit=1 and 
			--	(ProtectName is null) and ProtectId=1 And UpTime<DateAdd(day,@Days,GetDate()) And
			--	(CustomerName Not like '%免打扰%') And delFlag=0
		If @IndustryTwoId='0'	--一级行业为全部，并且二级行业为无行业分类时
		    If @CustomerTypeId='-1'--客户类型为-1为全部，当选择全部时不包含直通车
		    Select @num=Count(0) From Customer Where Companyid=@companyid and Audit=1 and 
			    (ProtectName is null) and ProtectId=1 And UpTime<DateAdd(day,@Days,GetDate()) And
			    (CustomerName Not like '%免打扰%') And delFlag=0 And OrdersCount=0
			    And IndustryTwoId=Convert(int,@IndustryTwoId) and TypeId!=8     --TypeId为8为直通车
			Else
			Select @num=Count(0) From Customer Where Companyid=@companyid and Audit=1 and 
			   (ProtectName is null) and ProtectId=1 And UpTime<DateAdd(day,@Days,GetDate()) And
			   (CustomerName Not like '%免打扰%') And delFlag=0 And OrdersCount=0
			   And IndustryTwoId=Convert(int,@IndustryTwoId) and TypeId=Convert(int,@CustomerTypeId) 
		Else
		   If @CustomerTypeId='-1'--客户类型为-1为全部，当选择全部时不包含直通车
		   Select @num=Count(0) From Customer Where Companyid=@companyid and Audit=1 and 
			   (ProtectName is null) and ProtectId=1 And UpTime<DateAdd(day,@Days,GetDate()) And
			   (CustomerName Not like '%免打扰%') And delFlag=0 And OrdersCount=0 and TypeId!=8     --TypeId为8为直通车
		   Else
		   Select @num=Count(0) From Customer Where Companyid=@companyid and Audit=1 and 
			   (ProtectName is null) and ProtectId=1 And UpTime<DateAdd(day,@Days,GetDate()) And
			   (CustomerName Not like '%免打扰%') And delFlag=0 And OrdersCount=0 and TypeId=Convert(int,@CustomerTypeId)

	Else	--指定的一级行业
		If @IndustryTwoId='-1'	--指定的一级行业,二级行业为全部
		      If @CustomerTypeId='-1'--客户类型为-1为全部，当选择全部时不包含直通车
			  Select @num=Count(0) From Customer Where Companyid=@companyid and Audit=1 and 
				(ProtectName is null) and ProtectId=1 And UpTime<DateAdd(day,@Days,GetDate()) And
				(CustomerName Not like '%免打扰%') And IndustryOneId=Convert(int,@IndustryOneId)
				And delFlag=0 And OrdersCount=0 and TypeId!=8     --TypeId为8为直通车
			  Else
			    Select @num=Count(0) From Customer Where Companyid=@companyid and Audit=1 and 
				(ProtectName is null) and ProtectId=1 And UpTime<DateAdd(day,@Days,GetDate()) And
				(CustomerName Not like '%免打扰%') And IndustryOneId=Convert(int,@IndustryOneId)
				And delFlag=0 And OrdersCount=0	and TypeId=Convert(int,@CustomerTypeId)
		Else	--指定的二级行业--二级行业参数可能为0，查询条件需要加上一级行业限定[顾世杰2013.10.23] 
			 If @CustomerTypeId='-1'--客户类型为-1为全部，当选择全部时不包含直通车
			 Select @num=Count(0) From Customer Where Companyid=@companyid and Audit=1 and 
				(ProtectName is null) and ProtectId=1 And UpTime<DateAdd(day,@Days,GetDate()) And
				(CustomerName Not like '%免打扰%') And IndustryOneId=Convert(int,@IndustryOneId) And 
				IndustryTwoId=Convert(int,@IndustryTwoId) And delFlag=0 And OrdersCount=0 and TypeId!=8     --TypeId为8为直通车
		     Else 
			  Select @num=Count(0) From Customer Where Companyid=@companyid and Audit=1 and 
				(ProtectName is null) and ProtectId=1 And UpTime<DateAdd(day,@Days,GetDate()) And
				(CustomerName Not like '%免打扰%') And IndustryOneId=Convert(int,@IndustryOneId) And 
				IndustryTwoId=Convert(int,@IndustryTwoId) And delFlag=0 And OrdersCount=0 and TypeId=Convert(int,@CustomerTypeId)
		select @num shl
		return @num
End

